﻿using System.Collections.Generic;
using SuperiorModel;
using SuperiorSqlTools;
using System.Data;
using System.Data.SqlClient;


namespace SuperiorShopDataAccess
{
    public class DataDataAccess
    {
        public static DataSet SearchShopOrderRankDs(ShopOrderRankCriteria criteria)
        {
            var sqlManager = new SqlManager();
            List<SqlParameter> parms = new List<SqlParameter>();
            var sb = sqlManager.CreateSb();
            if (!string.IsNullOrEmpty(criteria.LoginName))
            {
                sb.Append(" and LoginName=@LoginName");
                parms.Add(new SqlParameter("@LoginName", criteria.LoginName));
            }

            var sql = string.Format("select top({0}) * from(select ROW_NUMBER() over(order by TotalOrderCount desc)as rownum,* from (seleCt Id as ShopAdminId, LoginName,(select COUNT(*) from C_Order with(nolock) where ShopAdminId=s.Id and AppType=1) as ProgramOrderCount,(select COUNT(*) from C_Order with(nolock) where ShopAdminId=s.Id and AppType=2) as H5OrderCount,(select COUNT(*) from C_Order with(nolock) where ShopAdminId=s.Id) as TotalOrderCount   from C_ShopAdmin s  with(nolock) where {1} and OptionStatus=1 )a)tt where tt.rownum > {2}; select COUNT(*) totalCount from C_ShopAdmin where {1} and OptionStatus = 1", criteria.PagingResult.PageSize, sb.ToString(), criteria.PagingResult.PageSize * criteria.PagingResult.PageIndex);
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms.ToArray());
        }

        public static DataSet SearchShopUserRankDs(ShopUserRankCriteria criteria)
        {
            var sqlManager = new SqlManager();
            List<SqlParameter> parms = new List<SqlParameter>();
            var sb = sqlManager.CreateSb();
            if (!string.IsNullOrEmpty(criteria.LoginName))
            {
                sb.Append(" and LoginName=@LoginName");
                parms.Add(new SqlParameter("@LoginName", criteria.LoginName));
            }

            var sql = string.Format("select top({0}) * from(select ROW_NUMBER() over(order by TotalUserCount desc)as rownum,* from (seleCt Id as ShopAdminId, LoginName,(select COUNT(*) from C_Users with(nolock) where ShopAdminId=s.Id and AppType=1) as ProgramUserCount,(select COUNT(*) from C_Users with(nolock) where ShopAdminId=s.Id and AppType=2) as H5UserCount,(select COUNT(*) from C_Users with(nolock) where ShopAdminId=s.Id) as TotalUserCount   from C_ShopAdmin s  with(nolock) where {1} and OptionStatus=1 )a)tt where tt.rownum > {2}; select COUNT(*) totalCount from C_ShopAdmin where {1} and OptionStatus = 1", criteria.PagingResult.PageSize, sb.ToString(), criteria.PagingResult.PageSize * criteria.PagingResult.PageIndex);
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms.ToArray());
        }
        public static DataSet SearchShopAppExpressRankDs(ShopAppExpressRankCriteria criteria)
        {
            var sqlManager = new SqlManager();
            List<SqlParameter> parms = new List<SqlParameter>();
            var sb = sqlManager.CreateSb();
            if (criteria.AppType != 999)
            {
                sb.Append(" and csa.AppType=@AppType");
                parms.Add(new SqlParameter("@AppType", criteria.AppType));
            }
            if (!string.IsNullOrEmpty(criteria.LoginName))
            {
                sb.Append(" and s.LoginName=@LoginName");
                parms.Add(new SqlParameter("@LoginName", criteria.LoginName));
            }
            
            var sql = string.Format("select top({0}) * from(select ROW_NUMBER() over(order by ExpressTime desc)as rownum,* from (select csa.Id,csa.ShopAdminId,s.LoginName,csa.AppType,csa.ShopName,case csa.AppType when 1 then s.ProgrameExpireTime when 2 then s.H5ExpireTime Else Null End as ExpressTime from C_ShopApp csa left join C_ShopAdmin s  on csa.ShopAdminId = s.Id where {1} and csa.OptionStatus=1)a)tt where tt.rownum > {2};select COUNT(*) totalCount from C_ShopApp csa left join C_ShopAdmin s on csa.ShopAdminId = s.Id  where {1} and csa.OptionStatus = 1", criteria.PagingResult.PageSize, sb.ToString(), criteria.PagingResult.PageSize * criteria.PagingResult.PageIndex);
            return sqlManager.ExecuteDataset(CommandType.Text, sql, parms.ToArray());
        }
    }
}
